package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.domain.Deporte;
import pos.domain.Lugar;

public class JDBCLugarDAO implements ILugarDAO {
	//TODO Marta/Miguel Implementar metodos necesarios
	private Connection conn;
	
	public JDBCLugarDAO()
	{
		conn = ConnectionManager.getInstance().checkOut();
	}
	public JDBCLugarDAO(Connection conn)
	{
		this.conn = conn;
	}
	
	
	public Lugar selectLugar( String direccion){
		PreparedStatement stmt = null;
		ResultSet result = null;
		Lugar l = null;

		String sql = "SELECT * FROM lugares WHERE (OID = ?)";

		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, direccion.toUpperCase());
			result = stmt.executeQuery();
			
			result.next();
			l = new Lugar();
			l.setDireccion((result.getString("direccion")));
			l.setLatitud(result.getString("latitud"));
			l.setLongitud(result.getString("longitud"));
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		}finally {
			try {
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			} catch (SQLException e){
			}
		}
		return l;
	}
	
	public String selectLugarOID( String direccion){
		PreparedStatement stmt = null;
		ResultSet result = null;
	    String lugarOID=null;;
		String sql = "SELECT OID FROM Lugares WHERE (upper(direccion) like  ?) ";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, direccion.toUpperCase());
			result = stmt.executeQuery();
			
			result.next();
			lugarOID = result.getString("OID");
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		}finally {
			try {
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			} catch (SQLException e){
			}
		}
		return lugarOID;
	}
	public List selectAllLugares()
	{
		List lugares = new LinkedList();
        String sql = "SELECT * FROM lugares  ";

		PreparedStatement stmt = null;
        ResultSet result = null;
        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();
            while (result.next()) {
                Lugar l = new Lugar (result.getString("direccion"));
                lugares.add(l);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return lugares;
	}
}
